-- 创建系统模式
CREATE SCHEMA IF NOT EXISTS website;

-- 创建自动更新update_time的触发器函数
CREATE OR REPLACE FUNCTION website.auto_update_modify_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 0. 基础审计字段表
CREATE TABLE website.base_field (
    create_by BIGINT NOT NULL,           -- 创建人账号ID
    create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    update_by BIGINT NOT NULL,           -- 最后修改人账号ID
    update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP  -- 最后修改时间
);

-- 表注释
COMMENT ON TABLE website.base_field IS '基础审计字段表，包含所有表的公共审计字段';

-- 字段注释
COMMENT ON COLUMN website.base_field.create_by IS '创建人账号ID，关联website.account表的id字段';
COMMENT ON COLUMN website.base_field.create_time IS '记录创建时间，带时区信息';
COMMENT ON COLUMN website.base_field.update_by IS '最后修改人账号ID，关联website.account表的id字段';
COMMENT ON COLUMN website.base_field.update_time IS '记录最后修改时间，带时区信息，由触发器自动更新';

-- 添加更新时间触发器
CREATE TRIGGER trig_base_update_time
BEFORE UPDATE ON website.base_field
FOR EACH ROW EXECUTE FUNCTION website.auto_update_modify_time();


-- 1. 账号信息表（account）
CREATE TABLE website.account (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(64) NOT NULL UNIQUE, -- 登录账号名
    pwd VARCHAR(128) NOT NULL,           -- 加密存储的密码（含盐值）
    nickname VARCHAR(64) NOT NULL,       -- 账号昵称
    status SMALLINT NOT NULL,            -- 账号状态
    email VARCHAR(128),                  -- 邮箱（加密存储）
    phone VARCHAR(20),                   -- 手机号（加密存储）
    login_time TIMESTAMP WITH TIME ZONE, -- 最后登录时间
    create_by BIGINT NOT NULL,           -- 创建人账号ID
    create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    update_by BIGINT NOT NULL,           -- 最后修改人账号ID
    update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP  -- 最后修改时间
);

-- 表注释
COMMENT ON TABLE website.account IS '系统账号信息表，存储所有用户账号的核心信息';

-- 字段注释
COMMENT ON COLUMN website.account.id IS '账号唯一标识';
COMMENT ON COLUMN website.account.username IS '登录账号名';
COMMENT ON COLUMN website.account.pwd IS '加密后的密码（使用bcrypt等算法加盐存储）';
COMMENT ON COLUMN website.account.nickname IS '账号昵称（显示用）';
COMMENT ON COLUMN website.account.status IS '账号状态（1-正常，2-禁用，3-锁定）';
COMMENT ON COLUMN website.account.email IS '邮箱（敏感信息，加密存储）';
COMMENT ON COLUMN website.account.phone IS '手机号（敏感信息，加密存储）';
COMMENT ON COLUMN website.account.login_time IS '最后登录时间';
COMMENT ON COLUMN website.account.create_by IS '创建人账号ID';
COMMENT ON COLUMN website.account.create_time IS '创建时间';
COMMENT ON COLUMN website.account.update_by IS '最后修改人账号ID';
COMMENT ON COLUMN website.account.update_time IS '最后修改时间';

-- 添加更新时间触发器
CREATE TRIGGER trig_account_update_time
BEFORE UPDATE ON website.account
FOR EACH ROW EXECUTE FUNCTION website.auto_update_modify_time();

-- 2. 业务字典表（dict）
CREATE TABLE website.dict (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(64) NOT NULL UNIQUE,    -- 字典编码
    name VARCHAR(64) NOT NULL,           -- 字典名称
    description VARCHAR(255),            -- 字典描述
    status SMALLINT NOT NULL,            -- 字典状态
    remarks VARCHAR(255),                -- 字典备注
    create_by BIGINT NOT NULL,           -- 创建人账号ID
    create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    update_by BIGINT NOT NULL,           -- 最后修改人账号ID
    update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP  -- 最后修改时间
);

-- 表注释
COMMENT ON TABLE website.dict IS '业务字典表，存储系统中所有字典类型的定义';

-- 字段注释
COMMENT ON COLUMN website.dict.id IS '字典唯一标识';
COMMENT ON COLUMN website.dict.code IS '字典编码（如“ACCOUNT_STATUS”）';
COMMENT ON COLUMN website.dict.name IS '字典名称（如“账号状态”）';
COMMENT ON COLUMN website.dict.description IS '字典描述';
COMMENT ON COLUMN website.dict.status IS '字典状态（1-启用，2-停用）';
COMMENT ON COLUMN website.dict.remarks IS '字典备注';
COMMENT ON COLUMN website.dict.create_by IS '创建人账号ID';
COMMENT ON COLUMN website.dict.create_time IS '创建时间';
COMMENT ON COLUMN website.dict.update_by IS '最后修改人账号ID';
COMMENT ON COLUMN website.dict.update_time IS '最后修改时间';

-- 添加更新时间触发器
CREATE TRIGGER trig_dict_update_time
BEFORE UPDATE ON website.dict
FOR EACH ROW EXECUTE FUNCTION website.auto_update_modify_time();

-- 3. 字典数据项表（dict_item）
CREATE TABLE website.dict_item (
    id BIGSERIAL PRIMARY KEY,
    dict_id BIGINT NOT NULL,             -- 所属字典ID
    parent_id BIGINT,                    -- 所属父级数据项ID
    code VARCHAR(64) NOT NULL,           -- 数据项编码（同key）
    name VARCHAR(64) NOT NULL,           -- 数据项名称（同label）
    value VARCHAR(64) NOT NULL,          -- 数据项值
    sort SMALLINT NOT NULL DEFAULT 0,    -- 排序序号
    status SMALLINT NOT NULL DEFAULT 1,  -- 数据项状态（1-启用，2-停用）
    remarks VARCHAR(255),                -- 数据项备注
    create_by BIGINT NOT NULL,           -- 创建人账号ID
    create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    update_by BIGINT NOT NULL,           -- 最后修改人账号ID
    update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP  -- 最后修改时间
);

-- 表注释
COMMENT ON TABLE website.dict_item IS '字典数据项表，存储字典类型对应的具体数据项';

-- 字段注释
COMMENT ON COLUMN website.dict_item.id IS '数据项唯一标识';
COMMENT ON COLUMN website.dict_item.dict_id IS '所属字典ID';
COMMENT ON COLUMN website.dict_item.parent_id IS '所属父级数据项ID';
COMMENT ON COLUMN website.dict_item.code IS '数据项编码（如“NORMAL”）';
COMMENT ON COLUMN website.dict_item.value IS '数据项值（如“1”）';
COMMENT ON COLUMN website.dict_item.name IS '数据项名称（如“正常”）';
COMMENT ON COLUMN website.dict_item.sort IS '排序序号（越小越靠前）';
COMMENT ON COLUMN website.dict_item.status IS '数据项状态（1-启用，2-停用）';
COMMENT ON COLUMN website.dict_item.create_by IS '创建人账号ID';
COMMENT ON COLUMN website.dict_item.create_time IS '创建时间';
COMMENT ON COLUMN website.dict_item.update_by IS '最后修改人账号ID';
COMMENT ON COLUMN website.dict_item.update_time IS '最后修改时间';

-- 添加唯一约束
ALTER TABLE website.dict_item ADD CONSTRAINT uk_dict_item_code UNIQUE (dict_id, code);
ALTER TABLE website.dict_item ADD CONSTRAINT uk_dict_item_value UNIQUE (dict_id, value);

-- 添加更新时间触发器
CREATE TRIGGER trig_dict_item_update_time
BEFORE UPDATE ON website.dict_item
FOR EACH ROW EXECUTE FUNCTION website.auto_update_modify_time();

-- 4. 接口访问统计表（stat_api）
CREATE TABLE website.stat_api (
    id BIGSERIAL PRIMARY KEY,
    path VARCHAR(255) NOT NULL,          -- 接口路径
    method VARCHAR(10) NOT NULL,         -- HTTP方法
    access_count BIGINT NOT NULL DEFAULT 0,     -- 访问次数
    avg_time NUMERIC(10,2) NOT NULL DEFAULT 0.00, -- 平均响应时间(毫秒)
    max_time NUMERIC(10,2) NOT NULL DEFAULT 0.00, -- 最大响应时间(毫秒)
    stat_date DATE NOT NULL,             -- 统计日期
    create_by BIGINT NOT NULL,           -- 创建人账号ID
    create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    update_by BIGINT NOT NULL,           -- 最后修改人账号ID
    update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP  -- 最后修改时间
);

-- 表注释
COMMENT ON TABLE website.stat_api IS '接口访问统计表，记录系统API接口的访问情况和性能指标';

-- 字段注释
COMMENT ON COLUMN website.stat_api.id IS '统计记录唯一标识';
COMMENT ON COLUMN website.stat_api.path IS '接口路径（如“/api/user/list”）';
COMMENT ON COLUMN website.stat_api.method IS 'HTTP方法（GET/POST/PUT/DELETE）';
COMMENT ON COLUMN website.stat_api.access_count IS '访问次数（累计）';
COMMENT ON COLUMN website.stat_api.avg_time IS '平均响应时间（毫秒）';
COMMENT ON COLUMN website.stat_api.max_time IS '最大响应时间（毫秒）';
COMMENT ON COLUMN website.stat_api.stat_date IS '统计日期（按天聚合）';
COMMENT ON COLUMN website.stat_api.create_by IS '创建人账号ID';
COMMENT ON COLUMN website.stat_api.create_time IS '创建时间';
COMMENT ON COLUMN website.stat_api.update_by IS '最后修改人账号ID';
COMMENT ON COLUMN website.stat_api.update_time IS '最后修改时间';

-- 添加唯一约束
ALTER TABLE website.stat_api ADD CONSTRAINT uk_stat_api_unique UNIQUE (stat_date, path, method);

-- 添加更新时间触发器
CREATE TRIGGER trig_stat_api_update_time
BEFORE UPDATE ON website.stat_api
FOR EACH ROW EXECUTE FUNCTION website.auto_update_modify_time();

-- 5. 页面浏览统计表（stat_page）
CREATE TABLE website.stat_page (
    id BIGSERIAL PRIMARY KEY,
    path VARCHAR(255) NOT NULL,          -- 页面路径
    name VARCHAR(64) NOT NULL,           -- 页面名称
    view_count BIGINT NOT NULL DEFAULT 0, -- 浏览次数
    uv_count BIGINT NOT NULL DEFAULT 0,  -- 独立访问次数
    stat_date DATE NOT NULL,             -- 统计日期
    create_by BIGINT NOT NULL,           -- 创建人账号ID
    create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    update_by BIGINT NOT NULL,           -- 最后修改人账号ID
    update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP  -- 最后修改时间
);

-- 表注释
COMMENT ON TABLE website.stat_page IS '页面浏览统计表，记录系统页面的访问情况';

-- 字段注释
COMMENT ON COLUMN website.stat_page.id IS '统计记录唯一标识';
COMMENT ON COLUMN website.stat_page.path IS '页面路径（如“/pages/user/index”）';
COMMENT ON COLUMN website.stat_page.name IS '页面名称（如“用户管理页”）';
COMMENT ON COLUMN website.stat_page.view_count IS '浏览次数（累计）';
COMMENT ON COLUMN website.stat_page.uv_count IS '独立访问次数（按账号去重）';
COMMENT ON COLUMN website.stat_page.stat_date IS '统计日期（按天聚合）';
COMMENT ON COLUMN website.stat_page.create_by IS '创建人账号ID';
COMMENT ON COLUMN website.stat_page.create_time IS '创建时间';
COMMENT ON COLUMN website.stat_page.update_by IS '最后修改人账号ID';
COMMENT ON COLUMN website.stat_page.update_time IS '最后修改时间';

-- 添加唯一约束
ALTER TABLE website.stat_page ADD CONSTRAINT uk_stat_page_unique UNIQUE (stat_date, path);

-- 添加更新时间触发器
CREATE TRIGGER trig_stat_page_update_time
BEFORE UPDATE ON website.stat_page
FOR EACH ROW EXECUTE FUNCTION website.auto_update_modify_time();

-- 6. 账号登录日志表（log_login）
CREATE TABLE website.log_login (
    id BIGSERIAL PRIMARY KEY,
    account_id BIGINT NOT NULL,          -- 账号ID
    username VARCHAR(64) NOT NULL,        -- 登录账号名
    login_type SMALLINT NOT NULL,        -- 登录类型
    ip VARCHAR(64) NOT NULL,             -- 登录IP地址
    location VARCHAR(128),               -- 登录地点
    login_time TIMESTAMP WITH TIME ZONE NOT NULL, -- 登录时间
    logout_time TIMESTAMP WITH TIME ZONE, -- 登出时间
    create_by BIGINT NOT NULL,           -- 创建人账号ID
    create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    update_by BIGINT NOT NULL,           -- 最后修改人账号ID
    update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP  -- 最后修改时间
);

-- 表注释
COMMENT ON TABLE website.log_login IS '账号登录日志表，记录用户登录和登出系统的相关信息';

-- 字段注释
COMMENT ON COLUMN website.log_login.id IS '日志唯一标识';
COMMENT ON COLUMN website.log_login.account_id IS '账号ID';
COMMENT ON COLUMN website.log_login.username IS '登录账号名（冗余存储）';
COMMENT ON COLUMN website.log_login.login_type IS '登录类型（1-账号密码登录，2-短信登录，3-第三方登录）';
COMMENT ON COLUMN website.log_login.ip IS '登录IP地址';
COMMENT ON COLUMN website.log_login.location IS '登录地点（通过IP解析）';
COMMENT ON COLUMN website.log_login.login_time IS '登录时间';
COMMENT ON COLUMN website.log_login.logout_time IS '登出时间（未登出则为空）';
COMMENT ON COLUMN website.log_login.create_by IS '创建人账号ID';
COMMENT ON COLUMN website.log_login.create_time IS '创建时间';
COMMENT ON COLUMN website.log_login.update_by IS '最后修改人账号ID';
COMMENT ON COLUMN website.log_login.update_time IS '最后修改时间';

-- 添加更新时间触发器
CREATE TRIGGER trig_log_login_update_time
BEFORE UPDATE ON website.log_login
FOR EACH ROW EXECUTE FUNCTION website.auto_update_modify_time();

-- 7. 系统运维管理日志表（log_operate）
CREATE TABLE website.log_operate (
    id BIGSERIAL PRIMARY KEY,
    module VARCHAR(64) NOT NULL,         -- 操作模块
    oper_type SMALLINT NOT NULL,         -- 操作类型
    content TEXT NOT NULL,               -- 操作内容
    oper_ip VARCHAR(64) NOT NULL,        -- 操作IP地址
    account_id BIGINT NOT NULL,          -- 操作人账号ID
    username VARCHAR(64) NOT NULL,        -- 操作人账号名
    oper_time TIMESTAMP WITH TIME ZONE NOT NULL, -- 操作时间
    create_by BIGINT NOT NULL,           -- 创建人账号ID
    create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    update_by BIGINT NOT NULL,           -- 最后修改人账号ID
    update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP  -- 最后修改时间
);

-- 表注释
COMMENT ON TABLE website.log_operate IS '系统运维管理日志表，记录用户对系统进行的各类操作';

-- 字段注释
COMMENT ON COLUMN website.log_operate.id IS '日志唯一标识';
COMMENT ON COLUMN website.log_operate.module IS '操作模块（如“用户管理”、“角色配置”）';
COMMENT ON COLUMN website.log_operate.oper_type IS '操作类型（1-新增，2-修改，3-删除，4-查询，5-导入，6-导出）';
COMMENT ON COLUMN website.log_operate.content IS '操作内容（详细描述操作过程）';
COMMENT ON COLUMN website.log_operate.oper_ip IS '操作IP地址';
COMMENT ON COLUMN website.log_operate.account_id IS '操作人账号ID';
COMMENT ON COLUMN website.log_operate.username IS '操作人账号名';
COMMENT ON COLUMN website.log_operate.oper_time IS '操作时间';
COMMENT ON COLUMN website.log_operate.create_by IS '创建人账号ID';
COMMENT ON COLUMN website.log_operate.create_time IS '创建时间';
COMMENT ON COLUMN website.log_operate.update_by IS '最后修改人账号ID';
COMMENT ON COLUMN website.log_operate.update_time IS '最后修改时间';

-- 添加更新时间触发器
CREATE TRIGGER trig_log_operate_update_time
BEFORE UPDATE ON website.log_operate
FOR EACH ROW EXECUTE FUNCTION website.auto_update_modify_time();
